package br.com.portalecbahia.dao;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.Championship;
import br.com.portalecbahia.model.Ranking;
import br.com.portalecbahia.util.PortalECBahiaUtil;

public class RankingDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public RankingDAO(Context context) {
		this.context = context;
	}

	public RankingDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public RankingDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	
	
	public void insertByChampionship(JSONArray jsonArray, Championship championship) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{ 
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				
				cursor = database.rawQuery(" INSERT INTO tbl_rankings (position, team, points, games, wins, draws, losses, goalDifference, championshipId)  VALUES (" +
								"" +json.getInt("position")       +",  " +
								"'" +json.getString("team")       +"',  " +
								"" +json.getInt("points")         +",  " +
								"" +json.getInt("games")          +",  " +
								"" +json.getInt("wins")           +",  " +
								"" +json.getInt("draws")          +",  " +
								"" +json.getInt("losses")         +",  " +
								"" +json.getInt("goalDifference") +",  " +
								"" +championship.getId()+");", null);
					
					cursor.moveToFirst();
					cursor.close();
				
					
				
			}
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	public List<Ranking> findByChampionship (Championship championship) {
		this.openReadable();

		List<Ranking> result = new ArrayList<Ranking>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT position, team, points, games, wins, draws, losses, goalDifference, championshipId " +
									   "FROM tbl_rankings WHERE championshipId ="+championship.getId() +" "+
									   "order by position", null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	
	
	
	
	
	
	public void removeByChampionship(Championship championship) {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_rankings WHERE championshipId="+championship.getId(), null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}

	private Ranking bindSQLite(Cursor cursor){
		Ranking ranking  = new Ranking();
		ranking.setChampionship(new Championship());
		for (int i = 0; i < cursor.getColumnCount(); i++){
			
			if("championshipId".equals(cursor.getColumnName(i)))
				ranking.getChampionship().setId(cursor.getInt(i));
			else if("position".equals(cursor.getColumnName(i))) 
				ranking.setPosition(cursor.getInt(i));
			else if("team".equals(cursor.getColumnName(i))) 
				ranking.setTeam(cursor.getString(i));
			else if("points".equals(cursor.getColumnName(i))) 
				ranking.setPoints(cursor.getInt(i));
			else if("games".equals(cursor.getColumnName(i))) 
				ranking.setGames(cursor.getInt(i));
			else if("wins".equals(cursor.getColumnName(i))) 
				ranking.setWins(cursor.getInt(i));
			else if("draws".equals(cursor.getColumnName(i))) 
				ranking.setDraws(cursor.getInt(i));
			else if("losses".equals(cursor.getColumnName(i))) 
				ranking.setLosses(cursor.getInt(i));
			else if("goalDifference".equals(cursor.getColumnName(i))) 
				ranking.setGoalDifference(cursor.getInt(i));
			
		}
		return ranking;
	}


}
